package com.ruc.deke.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Component;

import com.ruc.deke.dao.DBCourseDAO;
import com.ruc.deke.model.DBRecord;
import com.ruc.deke.utils.ParseUtil;

@Component(value = "dbCourseDAOImpl")
public class DBCourseDAOImpl implements DBCourseDAO {

	@Resource(name = "dataSource_rucdbcourse")
	private DataSource dataSource;

	@Override
	public String insert(String tableName, DBRecord record) {
		int result = 0;
		String info = "success";
		Statement statement = null;
		Connection connection = null;
		try {
			String sql = ParseUtil.toInsertSQL(tableName, record);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			result = statement.executeUpdate(sql);
		} catch (SQLException e) {
			info = "failed";
			// e.printStackTrace();
			System.out.println(e.getMessage());
		} catch (Exception e) {
			System.out.println(e.getMessage());
			info = "failed";
			// e.printStackTrace();
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				info = "failed";
				System.out.println(e.getMessage());
				// e.printStackTrace();
			}
		}
		return info;
	}

	@Override
	public ArrayList<DBRecord> query(String sql) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();
		connection = dataSource.getConnection();
		statement = connection.createStatement();
		resultSet = statement.executeQuery(sql);
		records = ParseUtil.toDBRecord(resultSet);
		statement.close();
		connection.close();

		return records;
	}

	@Override
	public String queryOne(String sql) {
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		String returnValue = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				returnValue = rs.getString(1);
			}
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("query one exception:" + e.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqlexception) {
				// sqlexception.printStackTrace();
				System.out.println("exception:" + sqlexception.getMessage());
			}
		}
		return returnValue;
	}

	@Override
	public int insertUserFeedback(Connection conn, String tableName, DBRecord record) throws SQLException {
		// TODO Auto-generated method stub
		int result = 0;
		String info = "success";
		Statement statement = null;
		String sql = "";
		try {
			sql = ParseUtil.toInsertSQL(tableName, record);
		} catch (Exception e) {
			System.out.println("ParseUtil.SQL Exception:" + e.getMessage());
		}
		statement = conn.createStatement();
		result = statement.executeUpdate(sql);
		statement.close();
		return result;
	}

	@Override
	public String updateUserExpStatus(Connection conn, String tableName, DBRecord newRecord, DBRecord conditions)
			throws SQLException {
		Statement stmt = null;
		String result = "success";
		String sql = "";
		try {
			sql = ParseUtil.toUpdataSQL(tableName, newRecord, conditions);
		} catch (Exception e) {
			System.out.println("ParseUtil.toUpdateSQL Exception:" + e.getMessage());
		}
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
		stmt.close();
		return result;
	}

	@Override
	public ArrayList<DBRecord> getUniversities() {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from DB_COURSE_UNIVERSITY ";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public ArrayList<DBRecord> getMajors() {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from DB_COURSE_MAJOR ";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public String insertUserExpStatus(Connection conn, String tableName, DBRecord record) throws SQLException {
		int result = 0;
		String info = "success";
		Statement statement = null;
		String sql = "";
		try {
			sql = ParseUtil.toInsertSQL(tableName, record);
		} catch (Exception e) {
			System.out.println("ParseUtil.toUpdateSQL Exception:" + e.getMessage());
		}
		statement = conn.createStatement();
		result = statement.executeUpdate(sql);
		statement.close();
		return info;
	}

	@Override
	public String queryOne(Connection conn, String sql) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		String returnValue = null;

		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		if (rs.next()) {
			returnValue = rs.getString(1);
		}
		rs.close();
		stmt.close();

		return returnValue;

	}

	@Override
	public ArrayList<DBRecord> query(Connection conn, String sql) throws SQLException {
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		statement = conn.createStatement();
		resultSet = statement.executeQuery(sql);
		records = ParseUtil.toDBRecord(resultSet);
		statement.close();

		return records;
	}

	@Override
	public String insertUserModuleScore(Connection conn, String tableName, DBRecord record) throws SQLException {
		int result = 0;
		String info = "success";
		Statement statement = null;
		String sql = "";
		try {
			sql = ParseUtil.toInsertSQL(tableName, record);
		} catch (Exception e) {
			System.out.println("ParseUtil.SQL Exception:" + e.getMessage());
		}
		statement = conn.createStatement();
		result = statement.executeUpdate(sql);
		statement.close();
		return info;
	}

	@Override
	public int insertUserFeedbacklt(Connection conn, String tableName, DBRecord record) throws SQLException {
		// TODO Auto-generated method stub
		int result = 0;
		String info = "success";
		Statement statement = null;
		String sql = "";
		try {
			sql = ParseUtil.toInsertSQL(tableName, record);
		} catch (Exception e) {
			System.out.println("ParseUtil.SQL Exception:" + e.getMessage());
		}
		statement = conn.createStatement();
		result = statement.executeUpdate(sql);
		statement.close();
		return result;
	}

}
